// Created by Amit Mudgal (MicroStrategy Principal Consultant ) on 4/2/2014.
// This would be used for all SQL statement executions on Microsoft SQL Server

package com.amex.SQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

import com.amex.Crypto.Encryption;


public class DBOperations {

	private static final String DB_TYPE_NETEZZA;
	private static final String DB_TYPE_MSSQLSERVER;
	
	private static final String DB_TYPE;
	
	private static final String DRIVER_NAME;
	private static final String URL;
	private static final String USER;
	private static final String PASS;
	
	
	static {
		
		String host;
		String port;
		String dbName;
		String url;
		
        ResourceBundle props = ResourceBundle.getBundle("DBConnection");
        
        DB_TYPE = props.getString("DBDialect");
        
        DB_TYPE_NETEZZA = props.getString("nz_DBDialect");
        DB_TYPE_MSSQLSERVER = props.getString("ss_DBDialect");
        
        if(DB_TYPE_NETEZZA.equalsIgnoreCase(DB_TYPE)) {
        	
        	DRIVER_NAME = props.getString("nz_driverClass");
        	USER 		= props.getString("nz_uname");
        	PASS 		= Encryption.Decrypt(props.getString("nz_pword"));
        	
        	host 	= props.getString("nz_DBServer");
        	port 	= props.getString("nz_port");
        	dbName 	= props.getString("nz_DBName");
        	url 	= props.getString("nz_connURL");
			
        	// URL = "jdbc:netezza://" + host + ":" + port + "/" + dbName;
			URL = url + host + ":" + port + "/" + dbName;
        	
        } else if(DB_TYPE_MSSQLSERVER.equalsIgnoreCase(DB_TYPE)) {
        	
        	DRIVER_NAME = props.getString("ss_driverClass");
        	USER = props.getString("ss_uname");
        	PASS = Encryption.Decrypt(props.getString("ss_pword"));
        	
        	host 	= props.getString("ss_DBServer");
        	port 	= props.getString("ss_port");
        	dbName 	= props.getString("ss_DBName");
        	url		= props.getString("ss_connURL");
        	
			// URL = "jdbc:jtds:sqlserver://" + host + ":" + port + "/" + dbName;
        	URL = url + host + ":" + port + "/" + dbName;
			
        } else {
        	throw new RuntimeException("Unknow database type: " + DB_TYPE);
        }
        
	}
	
	// This method is used to connect to the Database.
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(URL, USER, PASS);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
   
	public static void disconnect(Connection conn, Statement stat, ResultSet res) {
		disconnect(res);
		disconnect(stat);
		disconnect(conn);
	}
	public static void disconnect(Connection conn, ResultSet res) {
		disconnect(res);
		disconnect(conn);
	}
	public static void disconnect(Connection conn, Statement stat) {
		disconnect(stat);
		disconnect(conn);
	}
	
	// This method is used to disconnect from the Database.	
	public static void disconnect(Connection conn) {
		try {
			conn.close();
		} catch (Throwable e) {
		}
	}
	
	public static void disconnect(Statement stat) {
		try {
			stat.close();
		} catch (Throwable e) {
		}
	}
	
	public static void disconnect(ResultSet res) {
		try {
			res.close();
		} catch (Throwable e) {
		}
	}
	
	/**
	 * @deprecated This method has a memory leak
	 * 
	 */
	// This method is used to run queries
	public static ResultSet execSQLQuery(Connection conn, String SQL) {
		ResultSet rs = null;
		try {
		  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		  rs = stmt.executeQuery (SQL);
		} catch (Exception e) {System.out.println("ERROR: While running Query ");}
		return rs;
	}
	
	
	
}


